Create Tables in Staging Database and Populate the Tables -Continued 8
· Drag and drop the sequence container.
· Drag Execute SQL Task inside the sequence container - Double Click and Add Connection manager and Write Sql Query
· Drag and drop the Data Flow Task inside the sequence container - Double Click
§ Drag and drop the Flat File Source and add the Flat File connection manager
§ Drag and drop the Data Conversion transformation - to convert the apprpriate Data Types
§ Drag and drop the OLEDB Destination - add the destination connection manager and select the staging table to load the data
·
Derived Column Transformation:
Drag and drop the sequence container.
Drag Execute SQL Task inside the sequence container - Double Click and Add Connection manager and Write Sql Query
· Drag and drop the Data Flow Task inside the sequence container - Double Click
§ Drag and drop the Excel Source and add Excel connection manager
§ Drag and drop the Data Conversion transformation - to convert the appropriate Data Types
§ Drag and drop the OLEDB Destination - add the destination connection manager and select the staging table to load the data
Drag and drop Execute SQL Task outside the sequence container - Double Click and Add Connection manager and Write Sql Query
·Drag and drop Sequence Container.
· Drag and drop the Data Flow Task inside the sequence container - Double Click
§ Drag and drop the Excel Source and add Excel connection manager
§ Drag and drop Derived Column Transformation
§ Drag and drop the Data Conversion transformation - to convert the appropriate Data Types
§ Drag and drop the OLEDB Destination - add the destination connection manager and select the staging table to load the data
Data Conversion:
Execute the Package in SSIS to load the Data into the Staging tables
The tables are populated with the data from the Source Files
SSIS Derived columns with multiple expressions
https://www.sqlshack.com/ssis-derived-columns-with-multiple-expressions-vs-multiple-transformations/